<?php
/**
 * Created by PhpStorm.
 * User: nightelf
 * Date: 2020/1/13
 * Time: 14:27
 */

namespace App\Http\Controllers\Excel;

use App\Exceptions\ApiException;
use App\Http\Controllers\Controller;
use App\Http\Model\EmployeeModel;
use App\Http\Model\MaterialCategoryModel;
use App\Http\Model\MaterialModel;
use App\Http\Model\MaterialSpecModel;
use App\Http\Model\ProfessionModel;
use App\Http\Model\ProjectModel;
use App\Http\Model\ProjectRosterModel;
use App\Http\Model\UnitModel;
use App\Http\Model\WarehouseLogInfoModel;
use App\Http\Model\WarehouseLogModel;
use App\Http\Model\WarehouseModel;
use App\Http\Model\YearModel;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;

class ImportController extends Controller
{
    private $column = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T'];
    private $template = [
        'employeeLists' => ['name', 'project', 'profession', 'jobNumber', 'idcard', 'gender', 'age', 'nation', 'phone', 'homeAddress', 'bankNumber', 'contractTime', 'eduTime'],
        'warehouseMaterial' => ['number', 'name', 'spec', 'unitName', 'amount', 'purchasePrice', 'totalPrice'],
        'materialLists' => ['number', 'categoryName', 'name', 'code', 'unitName', 'spec', 'specCode'],
    ];

    private $step = [
        'employeeLists' => 1,
        'warehouseMaterial' => 3,
        'materialLists' => 2,
    ];

    /**
     * @param $methodName
     * @return mixed
     */
    private function getTemplate($methodName)
    {
        $name = $this->getMethodName($methodName);
        return $this->template[$name];
    }

    /**
     * @param $methodName
     * @return mixed
     */
    private function getStep($methodName)
    {
        $name = $this->getMethodName($methodName);
        return $this->step[$name];
    }

    /**
     * 输出excel文件，返回文件名
     *
     * @param $data
     * @return string
     */
    private function storeExcel($data)
    {
        $fileName = time() . mt_rand(100, 999);
        $extensions = 'xlsx';
        Excel::create($fileName, function ($excel) use ($data) {
            $excel->sheet('sheet1', function ($sheet) use ($data) {
//                $sheet->rows($data);
                $sheet->fromArray($data, null, 'A1', true, false);
                $count = count($data);
                $sheet->setColumnFormat(
                    array(
                        'A1:K' . $count => '@',
                    )
                );
            });
        })->store($extensions);

        return $fileName . '.' . $extensions;
    }

    /**
     * 导入工人
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function employeeLists(Request $request)
    {
        $file = $request->file('file');
        $template = $this->getTemplate(__METHOD__);
        $step = $this->getStep(__METHOD__);
        $excel = Excel::load($file);
        //Excel 文件数据
        $excelData = $excel->getSheet()->toArray();

        //未成功导入的数据
        $errorData = [];

        $employeeModel = new EmployeeModel();
        $projectRosterModel = new ProjectRosterModel();
        $projectModel = new ProjectModel();
        $professionModel = new ProfessionModel();
        //遍历excel的数据
        foreach ($excelData as $key => $d) {
            //跳过表头
            if ($step != 0) {
                $step--;
                continue;
            }

            $insertData = [];
            $insert = true;
            $yearInfo = (new YearModel())->findYear(date('Y-m-d H:i:s'));
            foreach ($template as $k => $t) {
                $$t = $d[$k];
                switch ($t) {
                    case 'gender':
                        if ($$t == 1 || preg_match('/\x{7537}/u', $$t)) {
                            $insertData[$t] = 1;
                        } elseif ($$t == 2 || preg_match('/\x{5973}/u', $$t)) {
                            $insertData[$t] = 2;
                        } else {
                            $insertData[$t] = 0;
                        }
                        break;
                    case 'age':
                        $insertData[$t] = intval($$t);
                        break;
                    case 'jobNumber':
                        $result = $employeeModel->checkRepeat('jobNumber', $$t, $yearInfo['startTime'], $yearInfo['endTime']);
                        if ($result == 0) {
                            $insertData[$t] = $$t;
                        } else {
                            $insert = false;
                            break 2;
                        }
                        break;
                    case 'project':
                        $result = $projectModel->info(['name' => $$t]);
                        if ($result) {
                            $insertData[$t . 'Id'] = $result['id'];
                        } else {
                            $insert = false;
                            break 2;
                        }
                        break;
                    case 'profession':
                        $result = $professionModel->info(['name' => $$t]);
                        if ($result) {
                            $insertData[$t . 'Id'] = $result['id'];
                        } else {
                            $insert = false;
                            break 2;
                        }
                        break;
//                    case 'isContract':
//                        if ($$t == 1 || preg_match('/\x{662F}/u', $$t)) {
//                            $insertData[$t] = 1;
//                        } elseif ($$t == 0 || preg_match('/\x{5426}/u', $$t)) {
//                            $insertData[$t] = 0;
//                        }
//                        break;
//                    case 'isEdu':
//                        if ($$t == 1 || preg_match('/\x{662F}/u', $$t)) {
//                            $insertData[$t] = 1;
//                        } elseif ($$t == 0 || preg_match('/\x{5426}/u', $$t)) {
//                            $insertData[$t] = 0;
//                        }
//                        break;
//                    case 'contractTime':
//                        if (!empty($$t)){
//                            $insertData['isContract'] = 1;
//                            $insertData[$t] = $$t;
//                        }
//                        break;
//                    case 'eduTime':
//                        if (!empty($$t)){
//                            $insertData['isEdu'] = 1;
//                            $insertData[$t] = $$t;
//                        }
//                        break;
                    default:
                        $insertData[$t] = $$t;
                        break;
                }
            }

            if ($insert) {
                //插入员工数据
                $insertId = $employeeModel->insert($insertData);
                $projectRosterModel->roster([
                    'id' => $insertId,
                    'projectId' => null,
                    'professionId' => $insertData['professionId'],
                    'groupId' => null,
                    'hasAttendance' => 0
                ], $insertData['projectId']);
            } else {
                //保存插入错误的数据
                $errorData[] = $d;
            }
        }
        $count = count($errorData);
        if ($count > 0) {
            $filename = $this->storeExcel($errorData);
            $this->code = 510101;
            $this->msg = '共有 ' . $count . ' 条数据导入失败，点击<a href="http://' . $_SERVER['HTTP_HOST'] . '/excel/download?name=' . $filename . '&token=' . $request->input('token') . '">链接</a>查看失败的数据';
        }

        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * 导入仓库材料
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws \App\Exceptions\ApiException
     */
    public function warehouseMaterial(Request $request)
    {
        $file = $request->file('file');
        $template = $this->getTemplate(__METHOD__);
        $step = $this->getStep(__METHOD__);
        $excel = Excel::load($file);
        //Excel 文件数据
        $excelData = $excel->getSheet()->toArray();

        $projectModel = new ProjectModel();
        $materialModel = new MaterialModel();
        $materialSpecModel = new MaterialSpecModel();
        $unitModel = new UnitModel();
        $warehouseModel = new WarehouseModel();
        $warehouseLogModel = new WarehouseLogModel();

        $projectName = $excelData[1][1];
        $projectInfo = $projectModel->info(['name' => $projectName]);
        if (empty($projectInfo)) {
            throw new ApiException('无法找到对应的项目，请检查项目名称', 510201);
        }

        $totalAccount = 0;
        foreach ($excelData as $key => $d) {
            if ($step > 0) {
                --$step;
                continue;
            }

            foreach ($template as $k => $t) {
                $$t = $d[$k];
                switch ($t) {
                    case 'name':
                        if ($$t == '') {
                            throw new ApiException('序号' . $number . '的材料名称未填写', 510201);
                        }
                        $materialInfo = $materialModel->info(['name' => $$t]);
                        if (empty($materialInfo)) {
                            throw new ApiException('序号' . $number . '的材料名称与材料清单不匹配', 510201);
                        }
                        break;
                    case 'spec':
                        if ($$t == '') {
                            throw new ApiException('序号' . $number . '的材料规格未填写', 510201);
                        }
                        $specInfo = $materialSpecModel->info(['spec' => $$t, 'materialId' => $materialInfo['id']]);
                        if (empty($specInfo)) {
                            throw new ApiException('序号' . $number . '的材料规格与' . $name . '规格清单不匹配', 510202);
                        }
                        break;
                    case 'unitName':
                        if ($$t == '') {
                            throw new ApiException('序号' . $number . '的材料计量单位未填写', 510201);
                        }
                        $unitInfo = $unitModel->info(['name' => $$t]);
                        if (empty($unitInfo)) {
                            throw new ApiException('序号' . $number . '的计量单位无法在计量单位清单中查询到', 510203);
                        } else {
                            if ($unitInfo['id'] != $materialInfo['unitId']) {
                                throw new ApiException('序号' . $number . '的材料的计量单位与材料清单的默认计量单位不一致', 510203);
                            }
                        }
                        break;
                    case 'amount':
                        if ($$t == '') {
                            throw new ApiException('序号' . $number . '的材料数量未填写', 510201);
                        }
                        if (!is_numeric($$t)) {
                            throw new ApiException('序号' . $number . '的材料的数量不是数字', 510204);
                        }
                        break;
                    case 'purchasePrice':
                        if ($$t == '') {
                            throw new ApiException('序号' . $number . '的材料单价未填写', 510201);
                        }
                        if (!is_numeric($$t)) {
                            throw new ApiException('序号' . $number . '的材料的仓库进价不是数字', 510205);
                        }
                        break;
                }
            }

            $warehouseInfo = $warehouseModel->info(['materialId' => $materialInfo['id'], 'specId' => $specInfo['id'], 'projectId' => $projectInfo['id']]);
            $newAmount = $amount + (empty($warehouseInfo) ? 0 : $warehouseInfo['amount']);
            if (empty($warehouseInfo)) {
                $insertData[] = [
                    'projectId' => $projectInfo['id'],
                    'materialId' => $materialInfo['id'],
                    'specId' => $specInfo['id'],
                    'amount' => $newAmount,
                    'unitId' => $unitInfo['id'],
                    'purchasePrice' => $purchasePrice,
                    'buyType' => 0,
                    'updateTime' => date('Y-m-d H:i:s'),
                ];
            } else {
                $newPrice = round(($warehouseInfo['amount'] * $warehouseInfo['purchasePrice'] + $amount * $purchasePrice) / $newAmount, 2);
                $updateData[] = [
                    'id' => $warehouseInfo['id'],
                    'amount' => $newAmount,
                    'purchasePrice' => $newPrice,
                    'updateTime' => date('Y-m-d H:i:s'),
                ];
            }

            $infoData[] = [
                'materialId' => $materialInfo['id'],
                'specId' => $specInfo['id'],
                'supplierId' => 0,
                'amount' => $amount,
                'surplus' => $newAmount,
                'price' => $purchasePrice,
                'totalPrice' => $purchasePrice * $amount,
                'unitId' => $unitInfo['id'],
            ];

            $totalAccount += $amount * $purchasePrice;

        }

        DB::beginTransaction();
        try {
            //插入导入文件中仓库里不存在的新材料
            if (isset($insertData)) {
                $warehouseModel->insert($insertData);
            }
            //更新导入文件中仓库里已经存在的材料
            if (isset($updateData)) {
                foreach ($updateData as $ud) {
                    $warehouseInfoId = $ud['id'];
                    unset($ud['id']);
                    $warehouseModel->update(['id' => $warehouseInfoId], $ud);
                }
            }
            //插入出入库日志
            $warehouseLogModel->addLog([
                'projectId' => $projectInfo['id'],
                'price' => $totalAccount,
                'type' => 0,
                'time' => date('Y-m-d H:i:s'),
                'remark' => '文件导入',
                'data' => $infoData,
                'adminId' => $request->session()->get('adminId'),
            ]);
            DB::commit();
            return $this->ajaxResult($this->code, $this->msg, $this->data);
        } catch (\Exception $e) {
            DB::rollBack();
            throw new ApiException($e->getMessage(), 510206);
        }
    }

    /**
     * 系统设置的材料列表导入
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     */
    public function materialLists(Request $request)
    {
        $file = $request->file('file');
        $template = $this->getTemplate(__METHOD__);

        $materialCategoryModel = new MaterialCategoryModel();
        $materialModel = new MaterialModel();
        $materialSpecModel = new MaterialSpecModel();
        $unitModel = new UnitModel();
        $excel = Excel::load($file);
        //Excel 文件数据
        $sheetNames = $excel->getSheetNames();
        $data = [];
        foreach ($sheetNames as $n) {
            $step = $this->getStep(__METHOD__);
            $sheet = $excel->getSheetByName($n);
            $excelData = $sheet->toArray();
            $mergeCells = $sheet->getMergeCells();
            $categoryInfo = $materialCategoryModel->info(['name' => $n]);
            if (empty($categoryInfo)) {
                throw new ApiException('材料分类不存在', 510301);
            }

            foreach ($mergeCells as $kc => $cell) {
                $cellArray = $this->cellToIndex($cell);
                for ($r = $cellArray[0][0]; $r <= $cellArray[1][0]; $r++) {
                    for ($c = $cellArray[0][1]; $c <= $cellArray[1][1]; $c++) {
                        $excelData[$r][$c] = $excelData[$cellArray[0][0]][$cellArray[0][1]];
                    }
                }
            }
            foreach ($excelData as $k => $d) {
                if ($step > 0) {
                    --$step;
                    continue;
                } else {
                    if ($n != $d[1]) {
                        throw new ApiException($n . '分类第' . $d[0] . '个材料分类与工作簿名称不一致', 510303);
                    }
//                    if(in_array(null,$d)){
//                        throw new ApiException($n.'分类第'.$d[0].'个材料信息不完全',510302);
//                    }
                    $unit = $unitModel->info(['name' => $d[3]]);
                    if (!empty($unit)) {
                        $d[3] = $unit['id'];
                    }else{
                        $d[3] = 0;
                    }
//                    if ($d[7] == '租赁') {
//                        $d[7] = 1;
//                    } elseif ($d[7] == '自购' || $d[7] == '公司自购') {
//                        $d[7] = 0;
//                    }
                    $data[] = $d;
                }
            }
        }

        DB::beginTransaction();
        try {
            $lastCategory = $lastCategoryId = $lastMaterial = $lastMaterialId = '';
            $code = '00';
            $increment = 1;
            foreach ($data as $dd) {
                if ($lastCategory != $dd[1]) {
                    $categoryInfo = $materialCategoryModel->info(['name' => $dd[1]]);
                    $lastCategory = $dd[1];
                    $lastCategoryId = $categoryInfo['id'];
                    $increment = $categoryInfo['increment'];
                }
                //更新材料
                if ($lastMaterial != $dd[2]) {
                    $lastMaterial = $dd[2];
                    $materialInfo = $materialModel->info(['categoryId' => $lastCategoryId, 'name' => $dd[2]]);
                    if (empty($materialInfo)) {
                        $code = $increment < 10 ? '0' . $increment : $increment;
                        $lastMaterialId = $materialModel->insert([
                            'code' => $code,
                            'name' => $dd[2],
                            'categoryId' => $lastCategoryId,
//                        'increment'=>1,
                        ]);
                        $increment++;
                        DB::table('material_category')->where('id', $lastCategoryId)->update(['increment' => $increment]);
                    } else {
                        $lastMaterialId = $materialInfo['id'];
                    }
                }

                //更新规格
                $materialSpecModel->insert([
                    'spec' => $dd[4],
                    'categoryId' => $lastCategoryId,
                    'materialId' => $lastMaterialId,
                    'unitId' => $dd[3],
                ]);

            }
            //更新自增字段
//            $categoryList = $materialCategoryModel->lists();
//            foreach ($categoryList as $cl) {
//                $m = DB::table('material')->where('categoryId', $cl->id)->orderBy('code', 'desc')->first();
//                if (!empty($m)) {
//                    if ($cl->id != 3) {
//                        $increment = intval(substr($m->code, 1, 3)) + 1;
//                        DB::table('material_category')->where('id', $cl->id)->update(['increment' => $increment]);
//                    }
//                }
//            }

            DB::commit();
            return $this->ajaxResult($this->code, $this->msg, $this->data);
        } catch (\Exception $e) {
            DB::rollBack();
            throw new ApiException('[' . $e->getLine() . ']' . $e->getMessage(), 510305);
        }
    }

    /**
     * 拆分合并的单元格，转化为被合并的单元格的起点坐标和终点坐标
     * @param $cell
     * @return array
     */
    public function cellToIndex($cell)
    {
        $cellArray = explode(':', $cell);
        if (preg_match('/\d/', substr($cellArray[0], 1, 1))) {
            $cellStartColumn = array_search(substr($cellArray[0], 0, 1), $this->column);
            $cellStartRow = intval(substr($cellArray[0], 1));
        } else {
            $cellStartColumn = array_search(substr($cellArray[0], 0, 2), $this->column);
            $cellStartRow = intval(substr($cellArray[0], 2));
        }
        if (preg_match('/\d/', substr($cellArray[0], 1, 1))) {
            $cellEndColumn = array_search(substr($cellArray[1], 0, 1), $this->column);
            $cellEndRow = intval(substr($cellArray[1], 1));
        } else {
            $cellEndColumn = array_search(substr($cellArray[1], 0, 2), $this->column);
            $cellEndRow = intval(substr($cellArray[1], 2));
        }
        return [[$cellStartRow - 1, $cellStartColumn], [$cellEndRow - 1, $cellEndColumn]];
    }
}